package com.reef.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.reef.entity.Reader;
import com.reef.entity.Result;
import com.reef.service.StudentService;
@RestController
public class StudentExcelUtils {
	@Autowired
	private StudentService StudentService;
	/**
	 * 将excel表格李的数据使用poi技术导入到layui数据表格里
	 * @param file
	 * @param servletRequest
	 * @return
	 */
	@RequestMapping(value = "/importExcel",method =RequestMethod.POST)
	public Object importExcel(MultipartFile file,HttpServletRequest servletRequest) {
		//获取文件名字
	     String FileName = file.getOriginalFilename();
	     //上传文件之后的名字
	     String newFileName = System.currentTimeMillis()+FileName;
	     //获取上传文件的位置
	     String path = servletRequest.getServletContext().getRealPath("/upload");
	     //拼接路径
	     File tempFile = new File(path+File.separator+newFileName);
	     try {
	    	 // 上传
			file.transferTo(tempFile);
			//1.读取文件
			FileInputStream in = new FileInputStream(tempFile);
			//2.通过poi解析流HSSFWorkbook处理流得到的对象，就是封装的excel文件所有的数据
			XSSFWorkbook workBook=new XSSFWorkbook(in);
			//3.获取excel文件里面的工作表sheet
			XSSFSheet  sheet = workBook.getSheetAt(0);
			//4.获取工作表数据，从第二行数据开始读取，读到最后一行结束getLastRowNum()
			int lastRowNum = sheet.getLastRowNum();
			for(int i = 1;i<=lastRowNum;i++) {
				//获取每一行
				XSSFRow row = sheet.getRow(i);
				//获取每一行里面的单元格元素
				int r_id = Integer.valueOf((int) row.getCell(0).getNumericCellValue()) ;
				row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
				String name = row.getCell(1).getStringCellValue() ;
				String sex = row.getCell(2).getStringCellValue() ;
				row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
				String password = row.getCell(3).getStringCellValue() ;
				String profession = row.getCell(4).getStringCellValue() ;
				int borrowbook = Integer.valueOf((int) row.getCell(5).getNumericCellValue()) ;
				int givenbook = Integer.valueOf((int) row.getCell(6).getNumericCellValue()) ;
				int bigbook =  Integer.valueOf((int) row.getCell(7).getNumericCellValue()) ;
				String remark = row.getCell(8).getStringCellValue() ;
				int staus = Integer.valueOf((int) row.getCell(9).getNumericCellValue()) ;
				//将数据封装到实体
				Reader reader =new Reader(r_id, name, password, sex, borrowbook, givenbook, bigbook, profession, staus, remark);
				//调用添加方法 
				StudentService.save(reader);
			}
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	     
	     return new Result("上传成功","",true);
		}

	

	/**
	 * 从layui页面中将数据表格中的数据导出到excel里面。
	 * @return
	 */
	@RequestMapping(value = "/stuExport",method = RequestMethod.POST)
	public Object stuExport() {
		List<Reader> list = StudentService.selAll();
		String[] titles= {"学号","姓名","密码","性别","以借书量","归还书量","最大借书","专业","备注","状态"};
		//创建文件对象XSSFWorkbook
		XSSFWorkbook workbook = new XSSFWorkbook();
		//创建工作溥对象
		XSSFSheet sheet = workbook.createSheet();
		//创建标题（第一行）参数为下标，下表从零开始
		XSSFRow  titleRow = sheet.createRow(0);
		for (int i = 0; i < titles.length; i++) {
			XSSFCell cell =  titleRow.createCell(i);
			cell.setCellValue(titles[i]);
		}
		//将数据写入到excel
		for (int i = 1; i < list.size(); i++) {
			XSSFRow row = sheet.createRow(i);
			row.createCell(0).setCellValue(list.get(i).getR_id());
			row.createCell(1).setCellValue(list.get(i).getName());
			row.createCell(2).setCellValue(list.get(i).getPassword());
			row.createCell(3).setCellValue(list.get(i).getSex());
			row.createCell(4).setCellValue(list.get(i).getBorrowbook());
			row.createCell(5).setCellValue(list.get(i).getGivenbook());
			row.createCell(6).setCellValue(list.get(i).getBigbook());
			row.createCell(7).setCellValue(list.get(i).getProfession());
			row.createCell(8).setCellValue(list.get(i).getRemark());
			row.createCell(9).setCellValue(list.get(i).getStaus());
		}
		 
		//文件保存地址
		try {
			workbook.write(new FileOutputStream("D:/student.xlsx"));
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return new Result("导出成功_位置D:/","",true);
	}
}
